﻿using System;
using System.Collections.Generic;
using System.Text;
using POSPlus.PrintManager.Core;
using System.Data.SqlClient;

namespace POSPlus.PrintManager
{
	public class PrintManagerDisplay : DatabaseObject, IPrintManagerDisplay, IComparable<PrintManagerDisplay>
	{
		private int _displayID;
		private String _upc;
		private String _batchNo;
		private String _jobName;
		private int _locAisle;
		private int _locBlock;
		private int _locSeq;
		private String _type;
		private String _description;

		public PrintManagerDisplay(String connectionString)
			: base(connectionString)
		{
			PopulateDefault();
		}

		public PrintManagerDisplay(string connectionString, int displayID) 
			: base(connectionString)
		{
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerDisplays " +
					"WHERE DisplayID = @DisplayID", connection))
				{
					command.Parameters.AddWithValue("@DisplayID", displayID);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							DisplayID = FromDB<int>(reader["DisplayID"]);
							Upc = FromDB<string>(reader["Upc"]);
							JobName = FromDB<string>(reader["JobName"]);
							BatchNo = FromDB<string>(reader["BatchNo"]);
							LocAisle = FromDB<int>(reader["LocAisle"]);
							LocBlock = FromDB<int>(reader["LocBlock"]);
							LocSeq = FromDB<int>(reader["LocSeq"]);
						}
						else
						{
							PopulateDefault();
						}
					}
				}
				connection.Close();
			}
		}

		public PrintManagerDisplay(string connectionString, string upc, string jobName,
			int locAisle, int locBlock, int locSeq) : base(connectionString)
		{
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerDisplays " +
					"WHERE Upc = @Upc AND JobName = @JobName AND ISNULL(LocAisle, 0) = @LocAisle " +
					"AND ISNULL(LocBlock, 0) = @LocBlock AND ISNULL(LocSeq, 0) = @LocSeq", connection))
				{
					command.Parameters.AddWithValue("@Upc", upc);
					command.Parameters.AddWithValue("@JobName", jobName);
					command.Parameters.AddWithValue("@LocAisle", locAisle);
					command.Parameters.AddWithValue("@LocBlock", locBlock);
					command.Parameters.AddWithValue("@LocSeq", locSeq);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							Upc = FromDB<string>(reader["Upc"]);
							JobName = FromDB<string>(reader["JobName"]);
							BatchNo = FromDB<string>(reader["BatchNo"]);
							LocAisle = FromDB<int>(reader["LocAisle"]);
							LocBlock = FromDB<int>(reader["LocBlock"]);
							LocSeq = FromDB<int>(reader["LocSeq"]);
							Type = FromDB<string>(reader["Type"]);
						}
						else
						{
							PopulateDefault();
						}
					}
				}
				connection.Close();
			}
		}

		public PrintManagerDisplay(String connectionString, String upc, String jobName)
			: base(connectionString)
		{
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerDisplays " +
					"WHERE Upc = @Upc AND JobName = @JobName", connection))
				{
					command.Parameters.AddWithValue("@Upc", upc);
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							Upc = FromDB<String>(reader["Upc"]);
							JobName = FromDB<String>(reader["JobName"]);
							BatchNo = FromDB<String>(reader["BatchNo"]);
							LocAisle = FromDB<int>(reader["LocAisle"]);
							LocBlock = FromDB<int>(reader["LocBlocK"]);
							LocSeq = FromDB<int>(reader["LocSeq"]);
							Type = FromDB<String>(reader["Type"]);
						}
						else
						{
							PopulateDefault();
						}
					}
				}
				connection.Close();
			}
		}

		public static List<PrintManagerDisplay> GetAll(string connectionString, string jobName)
		{
			List<PrintManagerDisplay> result = new List<PrintManagerDisplay>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT a.*, b.Description FROM (SELECT * FROM POSplus_PrintManagerDisplays WHERE JobName = @JobName) a INNER JOIN (SELECT Upc, Description FROM Products) b ON a.Upc = b.Upc ",
					connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							PrintManagerDisplay display = new PrintManagerDisplay(connectionString);
							display.DisplayID = FromDB<int>(reader["DisplayID"]);
							display.BatchNo = FromDB<String>(reader["BatchNo"]);
							display.JobName = FromDB<String>(reader["JobName"]);
							display.LocAisle = FromDB<int>(reader["LocAisle"]);
							display.LocBlock = FromDB<int>(reader["LocBlock"]);
							display.LocSeq = FromDB<int>(reader["LocSeq"]);
							display.Type = FromDB<String>(reader["Type"]);
							display.Upc = FromDB<String>(reader["Upc"]);
							display.Description = FromDB<string>(reader["Description"]);
							result.Add(display);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		public int DisplayID
		{
			get { return _displayID; }
			private set { _displayID = value; }
		}

		public string Upc
		{
			get { return _upc; }
			set { _upc = value; }
		}

		public string BatchNo
		{
			get { return _batchNo; }
			set { _batchNo = value; }
		}

		public string JobName
		{
			get { return _jobName; }
			set { _jobName = value; }
		}

		public int LocAisle
		{
			get { return _locAisle; }
			set { _locAisle = value; }
		}

		public int LocBlock
		{
			get { return _locBlock; }
			set { _locBlock = value; }
		}

		public int LocSeq
		{
			get { return _locSeq; }
			set {_locSeq = value;}
		}

		public string Type
		{
			get { return _type; }
			set { _type = value; }
		}

		public string Description
		{
			get { return _description; }
			set { _description = value; }
		}
		public override bool Create()
		{
			if (Upc == "") return false;
			int rowsAffected = -1;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("INSERT INTO POSplus_PrintManagerDisplays (Upc, BatchNo," +
					"JobName,LocAisle,LocBlock,LocSeq,Type) VALUES (@Upc, @BatchNo, @JobName, @LocAisle, @LocBlock," +
					"@LocSeq,@Type)", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@BatchNo", BatchNo);
					command.Parameters.AddWithValue("@JobName", JobName);
					command.Parameters.AddWithValue("@LocAisle", LocAisle);
					command.Parameters.AddWithValue("@LocBlock", LocBlock);
					command.Parameters.AddWithValue("@LocSeq", LocSeq);
					command.Parameters.AddWithValue("@Type", Type);
					try
					{
						rowsAffected = command.ExecuteNonQuery();
					}
					catch (SqlException)
					{
						rowsAffected = -1;
					}
				}
			}
			return rowsAffected > 0;
		}

		public override bool Update()
		{
			int rowsAffected = -1;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("UPDATE POSplus_PrintManagerDisplays SET " +
					"Upc = @Upc, BatchNo = @BatchNo, JobName = @JobName, LocAisle = @LocAisle, " +
					"LocBlock = @LocBlock, LocSeq = @LocSeq, Type = @Type WHERE DisplayID = @DisplayID",
					connection))
				{
					command.Parameters.AddWithValue("@DisplayID", DisplayID);
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@BatchNo", BatchNo);
					command.Parameters.AddWithValue("@JobName", JobName);
					command.Parameters.AddWithValue("@LocAisle", LocAisle);
					command.Parameters.AddWithValue("@LocBlock", LocBlock);
					command.Parameters.AddWithValue("@LocSeq", LocSeq);
					command.Parameters.AddWithValue("@Type", Type);
					try
					{
						rowsAffected = command.ExecuteNonQuery();
					}
					catch (SqlException)
					{
						rowsAffected = -1;
					}
				}
				connection.Close();
			}
			return rowsAffected > 0;
		}

		internal static void DeleteAll(string connectionString, int[] ids)
		{
			using(SqlConnection connection = new SqlConnection(connectionString))
			{
				string stringIds = "";
				foreach (int id in ids)
				{
					stringIds += "'" + id.ToString() + "',";
				}

				stringIds = stringIds.Substring(0, stringIds.Length - 1);

				connection.Open();
				using (SqlCommand command = new SqlCommand("DELETE FROM POSplus_PrintManagerDisplays " +
					"WHERE DisplayID IN (" + stringIds + ")", connection))
				{
					try
					{
						command.ExecuteNonQuery();
					}
					catch (SqlException)
					{
						throw;
					}
				}
				connection.Close();
			}
		}

        public static void DeleteWithJobName(String connectionString, String jobName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("DELETE FROM POSplus_PrintManagerDisplays " +
                    "WHERE JobName = @JobName", connection))
                {
                    command.Parameters.AddWithValue("@JobName", jobName);

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (SqlException)
                    {
                        throw;
                    }
                }
                connection.Close();
            }
        }

		public override bool Delete()
		{
			int rowsAffected = -1;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using(SqlCommand command = new SqlCommand("DELETE FROM POSplus_PrintManagerDisplays WHERE " +
					"DisplayID = @DisplayID", connection))
				{
					command.Parameters.AddWithValue("@DisplayID", DisplayID);
					try
					{
						rowsAffected = command.ExecuteNonQuery();
					}
					catch (SqlException)
					{
						rowsAffected = -1;
					}
				}
				connection.Close();
			}
			return rowsAffected > 0;
		}

		public override void PopulateDefault()
		{
			Upc = "";
			Type = "";
			LocSeq = -1;
			LocBlock = -1;
			LocAisle = -1;
			JobName = "";
			BatchNo = "";
		}

		public int CompareTo(PrintManagerDisplay other)
		{
			if (LocAisle == 0 && other.LocAisle != 0)
				return 1;

			if (LocAisle != 0 && other.LocAisle == 0)
				return -1;

			if (LocAisle > other.LocAisle)
				return 1;
			else if (LocAisle < other.LocAisle)
				return -1;

			if (LocBlock > other.LocBlock)
				return 1;
			else if (LocBlock < other.LocBlock)
				return -1;

			if (LocSeq > other.LocSeq)
				return 1;
			else if (LocSeq < other.LocSeq)
				return -1;

			return Upc.CompareTo(other.Upc);
		}
	}
}
